/**
 * 
 */
package com.feib.stms.dao.jdbc;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.GroupDao;
import com.feib.stms.dao.jdbc.rowmapper.GroupRowMapper;
import com.feib.stms.model.Group;
import com.feib.stms.pagination.PaginatedList;
import com.feib.stms.util.UserUtil;
/**
 * @author Lion Gao
 *
 */
@Repository("groupDao")
public class GroupJdbcDao extends BaseJdbcDao implements GroupDao 
{
	private OracleSequenceMaxValueIncrementer sequence;

	private SimpleJdbcInsert groupJdbcInsert;

	@Override
	protected void initDao() throws Exception {
		super.initDao();

		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "APP_GROUP_PK_SEQ");
		this.groupJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.groupJdbcInsert.withTableName("APP_GROUP")
		.usingColumns("GROUP_PK", "GROUP_NO", "GROUP_NAME", "GROUP_TYPE", "GROUP_NOTE"
				, "CONTACT_NAME", "CONTACT_EMAIL", "CONTACT_TELNO", "CONTACT_CELLNO", "GROUP_ADDRESS"
				, "UBM", "CREATED_BY" ,"CREATED_DATE" ,"UPDATED_BY" ,"UPDATED_DATE");
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#getByGroupPk(java.lang.Long)
	 */
	public Group getByGroupPk(Long groupPk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT G.*");
		sql.append(" FROM APP_GROUP G");
		sql.append(" WHERE G.GROUP_PK = :GROUP_PK");
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new GroupRowMapper(this), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#getByGroupNo(java.lang.String)
	 */
	public Group getByGroupNo(String groupNo) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT G.*");
		sql.append(" FROM APP_GROUP G");
		sql.append(" WHERE G.GROUP_NO = :GROUP_NO");
		parameters.addValue("GROUP_NO",  groupNo, Types.VARCHAR);
		return DataAccessUtils.singleResult( this.getSimpleJdbcTemplate().query(sql.toString(), new GroupRowMapper(this), parameters));
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#insert(com.feib.stms.model.Group)
	 */
	public int insert(Group group) throws DataAccessException {
		int insertCounts = 0;
		try{
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			Long groupPk = new Long(this.sequence.nextLongValue());
			parameters.addValue("GROUP_PK", groupPk, Types.NUMERIC);
			parameters.addValue("GROUP_NO", group.getGroupNo(), Types.VARCHAR);
			parameters.addValue("GROUP_NAME", group.getGroupName(), Types.VARCHAR);
			parameters.addValue("GROUP_TYPE", 2, Types.NUMERIC);//券商默認為2
			parameters.addValue("GROUP_NOTE",  group.getGroupNote(), Types.VARCHAR);
			parameters.addValue("CONTACT_NAME",  group.getContactName(), Types.VARCHAR);
			parameters.addValue("CONTACT_EMAIL",  group.getContactEmail(), Types.VARCHAR);
			parameters.addValue("CONTACT_TELNO",  group.getContactTelno(), Types.VARCHAR);
			parameters.addValue("CONTACT_CELLNO",  group.getContactCellno(), Types.VARCHAR);
			parameters.addValue("GROUP_ADDRESS",   group.getGroupAddress(), Types.VARCHAR);
			parameters.addValue("UBM",  group.getUbm(), Types.VARCHAR);
			parameters.addValue("CREATED_BY",  group.getCreatedBy(), Types.VARCHAR);
			parameters.addValue("CREATED_DATE",  group.getCreatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_BY", group.getUpdatedBy(), Types.VARCHAR);
			parameters.addValue("UPDATED_DATE",  group.getUpdatedDate(), Types.TIMESTAMP);
			insertCounts = groupJdbcInsert.execute(parameters);
			if (insertCounts > 0)
				group.setGroupPk(groupPk);
			return insertCounts;
		}catch (DataAccessException e) {
			logger.error(e.getMessage(), e);
		}
		return 0;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#queryForPaginatedListByGroup(com.feib.stms.model.Group, com.feib.stms.pagination.PaginatedList)
	 */
	public PaginatedList<Group> queryForPaginatedListByGroup(Group group, PaginatedList<Group> paginatedList) {
		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderByGroup(group, false, paginatedList);		
		return this.queryForPaginatedList(sqlQueryProvider, new GroupRowMapper(this), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}

	/*
	 * 拼接分頁查詢sql
	 */
	protected SqlQueryProvider getSqlQueryProviderByGroup(Group group, boolean excludeBank, PaginatedList<Group> paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();

		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();

		sql.append("SELECT *");
		sql.append(" FROM APP_GROUP WHERE 1 = 1");

		// 排除 銀行
		if (excludeBank)
		{
			sql.append(" AND GROUP_TYPE != ").append(Group.GROUP_TYPE_BANK);
		}

		if (null != group)
		{	
			/* 券商代號 */
			if(!StringUtils.isEmpty(group.getGroupNo())){
				sql.append(" AND GROUP_NO = ?");
				args.add(group.getGroupNo());
			}
			/* 券商名稱 */
			if(!StringUtils.isEmpty(group.getGroupName())){
				sql.append(" AND GROUP_NAME LIKE ?");
				args.add("%" + group.getGroupName() + "%");
			}			
		}
		//非銀行端，則不能全查，只能查詢自己所屬Group
		if(!UserUtil.isBankGroup()){
			sql.append(" AND GROUP_PK = ?");
			args.add(UserUtil.getUserGroup().getGroupPk());
		}
		/* 排序 */
		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		return sqlQueryProvider;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#update(com.feib.stms.model.Group)
	 */
	public int update(Group group) {
		StringBuffer sql = new StringBuffer();
		if(group != null){
			String userId = UserUtil.getUserId();
			group.setUpdatedBy(group.getGroupNo() + "_" +userId);
			group.setUpdatedDate(new Date());
		}
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE APP_GROUP SET GROUP_NO = :GROUP_NO,GROUP_NAME = :GROUP_NAME ,GROUP_NOTE = :GROUP_NOTE");
		sql.append(",CONTACT_NAME = :CONTACT_NAME, CONTACT_EMAIL = :CONTACT_EMAIL,CONTACT_TELNO = :CONTACT_TELNO ");
		sql.append(",CONTACT_CELLNO = :CONTACT_CELLNO,GROUP_ADDRESS = :GROUP_ADDRESS,UBM = :UBM, UPDATED_BY = :UPDATED_BY, UPDATED_DATE = :UPDATED_DATE ");
		sql.append(" WHERE GROUP_PK = :GROUP_PK");

		parameters.addValue("GROUP_NO",  group.getGroupNo(), Types.VARCHAR);
		parameters.addValue("GROUP_NAME", group.getGroupName(), Types.VARCHAR);
		parameters.addValue("GROUP_NOTE",  group.getGroupNote(), Types.VARCHAR);
		parameters.addValue("CONTACT_NAME",  group.getContactName(), Types.VARCHAR);
		parameters.addValue("CONTACT_EMAIL",  group.getContactEmail(), Types.VARCHAR);
		parameters.addValue("CONTACT_TELNO",  group.getContactTelno(), Types.VARCHAR);
		parameters.addValue("CONTACT_CELLNO",  group.getContactCellno(), Types.VARCHAR);
		parameters.addValue("GROUP_ADDRESS",   group.getGroupAddress(), Types.VARCHAR);
		parameters.addValue("UBM",  group.getUbm(), Types.VARCHAR);
		parameters.addValue("UPDATED_BY",  group.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  group.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("GROUP_PK",   group.getGroupPk(), Types.NUMERIC);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#countGroupNo(java.lang.Long, java.lang.String)
	 */
	public int countGroupNo(Long groupPk, String groupNo) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM APP_GROUP WHERE GROUP_NO = :GROUP_NO AND GROUP_PK != :GROUP_PK");
		parameters.addValue("GROUP_NO",  groupNo, Types.VARCHAR);
		parameters.addValue("GROUP_PK",  groupPk, Types.NUMERIC);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.GroupDao#countGroupNo(java.lang.String)
	 */
	public int countGroupNo( String groupNo) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM APP_GROUP WHERE GROUP_NO = :GROUP_NO");
		parameters.addValue("GROUP_NO",  groupNo, Types.VARCHAR);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}

	public PaginatedList<Group> queryForPaginatedListByGroupExcludeBank(
			Group group, PaginatedList<Group> paginatedList) {

		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderByGroup(group, true, paginatedList);		
		return this.queryForPaginatedList(sqlQueryProvider, new GroupRowMapper(this), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}

	public List<Group> queryAllGroup() {
		/* 查詢SQL */
		StringBuffer sql = new StringBuffer();

		sql.append("select * from APP_GROUP order by group_no");

		return this.getJdbcTemplate().query(sql.toString(), new Object[]{}, new GroupRowMapper(this));
	}

}
